﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data;
using System.Text;
using System.Data.SQLite;

/// <summary>
///SQLiteHelper 的摘要说明
/// </summary>
public static class SQLiteHelper
{
    /// <summary>
    /// 获得连接对象
    /// </summary>
    /// <returns></returns>
    public static SQLiteConnection GetSQLiteConnection()
    {        
        return new SQLiteConnection("Data Source=" 
            + System.AppDomain.CurrentDomain.BaseDirectory 
            + System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString());

    }

    private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, SQLiteParameter[] p)
    {
        if (conn.State != ConnectionState.Open)
            conn.Open();
        
        cmd.Connection = conn;
        cmd.CommandText = cmdText;
        cmd.CommandType = CommandType.Text;
        cmd.CommandTimeout = 30;
        cmd.Parameters.Clear();

        if (p != null)
        {
            cmd.Parameters.AddRange(p);
        }
    }

    public static DataSet ExecuteDataset(string cmdText, SQLiteParameter[] p)
    {
        DataSet ds = new DataSet();
        SQLiteCommand command = new SQLiteCommand();

        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            PrepareCommand(command, connection, cmdText, p);
            SQLiteDataAdapter da = new SQLiteDataAdapter(command);
            da.Fill(ds);
        }
        return ds;
    }

    public static DataRow ExecuteDataRow(string cmdText, SQLiteParameter[] p)
    {
        DataSet ds = ExecuteDataset(cmdText, p);

        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            return ds.Tables[0].Rows[0];

        return null;
    }



    /// <summary>
    /// 返回受影响的行数
    /// </summary>
    /// <param name="cmdText">a</param>
    /// <param name="commandParameters">传入的参数</param>
    /// <returns></returns>
    public static int ExecuteNonQuery(string cmdText, SQLiteParameter[] p)
    {
        SQLiteCommand command = new SQLiteCommand();

        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            PrepareCommand(command, connection, cmdText, p);
            return command.ExecuteNonQuery();
        }
    }

    /// <summary>
    /// 返回SqlDataReader对象
    /// </summary>
    /// <param name="cmdText"></param>
    /// <param name="commandParameters">传入的参数</param>
    /// <returns></returns>
    public static SQLiteDataReader ExecuteReader(string cmdText, SQLiteParameter[] p)
    {
        SQLiteCommand command = new SQLiteCommand();
        SQLiteConnection connection = GetSQLiteConnection();

        try
        {
            PrepareCommand(command, connection, cmdText, p);
            SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        catch
        {
            connection.Close();
            throw;
        }
    }



    /// <summary>
    /// 返回结果集中的第一行第一列，忽略其他行或列
    /// </summary>
    /// <param name="cmdText"></param>
    /// <param name="commandParameters">传入的参数</param>
    /// <returns></returns>
    public static object ExecuteScalar(string cmdText, SQLiteParameter[] p)
    {
        SQLiteCommand cmd = new SQLiteCommand();

        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            PrepareCommand(cmd, connection, cmdText, p);
            return cmd.ExecuteScalar();
        }
    }



    /// <summary>
    /// 分页
    /// </summary>
    /// <param name="recordCount"></param>
    /// <param name="pageIndex"></param>
    /// <param name="pageSize"></param>
    /// <param name="cmdText"></param>
    /// <param name="countText"></param>
    /// <param name="p"></param>
    /// <returns></returns>
    public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, SQLiteParameter[] p)
    {

        if (recordCount < 0)
            recordCount = int.Parse(ExecuteScalar(countText, p).ToString());

        DataSet ds = new DataSet();
        SQLiteCommand command = new SQLiteCommand();

        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            PrepareCommand(command, connection, cmdText, p);
            SQLiteDataAdapter da = new SQLiteDataAdapter(command);
            da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
        }
        return ds;
    }
}